package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;


import beans.MyEventBean;
import beans.UserBean;

/** Based on MessageDAO
It handles all the queries of MyEvents.jsp
It provides 4 methods
-gethostupcoming(UserBean User) --> provides all the upcoming hosted events
-gethostpast(UserBean User) --> provides all the past hosted events
-getguestupcoming(UserBean User) --> provides all the upcoming events as guest
-getguestpast(UserBean User) --> provides all the past events as guest


@author Felix Mendoza
*/


public class MyEventDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;
	
	
	// method to get the upcoming hosted events 
 	public static ArrayList<MyEventBean> gethostupcoming(UserBean user) {
	
 	// preparing some objects for connection
 		    Statement stmt = null;

			String username = user.getEmail();
			String searchQuery = null; 
        
			
			//once the rating system is working we have to change this query
			searchQuery = "select name, starttimestamp, numberguestspots, numberparticipants from event where host ='" + username + "'and isfinished='false'" ;
			
			System.out.println("Fetching the event for " + username);
			System.out.println("Query: " + searchQuery);

			ArrayList<MyEventBean> hostupcomingList = new ArrayList<MyEventBean>();
 			
 
 			try {
	 				// connect to DB
	 				currentCon = ConnectionManager.getConnection();
	 				stmt = currentCon.createStatement();
	 				rs = stmt.executeQuery(searchQuery);

	 			  //once the rating sysmtem is working, uncomment the rating attributes
	 				while (rs.next()) {
	 					MyEventBean myevent = new MyEventBean();

	 					

	 					myevent.setEventname(rs.getString(1));
	 					myevent.setStamp(rs.getTimestamp(2));
	 					myevent.setnGuestspots(rs.getInt(3));
	 					myevent.setnParticipants(rs.getInt(4));
	 					
	 					hostupcomingList.add(myevent);

	  					// Debug
	 					System.out.println(myevent.getEventname());
	 					System.out.println(myevent.getStamp());
	 					System.out.println(myevent.getnGuestspots());
	 					System.out.println(myevent.getnParticipants());
	 					
	 					}
	 			}
	 			catch (Exception ex) {
	 				System.out.println("FAIL!: An Exception has occurred on gethostupcoming! " + ex);
	 			}
	 			
	 			finally {
	 				if (rs != null) {
	 					try {
	 						rs.close();
	 					} catch (Exception e) {
	 					}
	 					rs = null;
	 				}

	 				if (stmt != null) {
	 					try {
	 						stmt.close();
	 					} catch (Exception e) {
	 					}
	 					stmt = null;
	 				}

	 				if (currentCon != null) {
	 					try {
	 						currentCon.close();
	 					} catch (Exception e) {
	 					}

	 					currentCon = null;
	 				}
	 			}

 			
 			return hostupcomingList;

 		
	}
 	
 	
 	public static ArrayList<MyEventBean> gethostpast(UserBean user) {
 		
 	 	// preparing some objects for connection
 	 			Statement stmt = null;

 	 			String username = user.getEmail();
 	 			String searchQuery = null; 
                
 	 			
 	 			//once the rating system is working we have to change this query
 	 			searchQuery = "select name, starttimestamp, numberguestspots, numberparticipants, avg(eventrating) from (select name, starttimestamp, numberguestspots, numberparticipants from event where host ='" + username + "' and isfinished='true') as table1 natural join (select host, avg(hostrating) as eventrating from attend group by host) as table2 group by table1.name, table1.starttimestamp,table1.numberguestspots,table1.numberparticipants";
 	 			
 	 			System.out.println("Fetching the event for " + username);
 	 			System.out.println("Query: " + searchQuery);

 	 			ArrayList<MyEventBean> hostpastList = new ArrayList<MyEventBean>();
 	 			
 	 
 	 			try {
 	 				// connect to DB
 	 				currentCon = ConnectionManager.getConnection();
 	 				stmt = currentCon.createStatement();
 	 				rs = stmt.executeQuery(searchQuery);

 	 			  //once the rating sysmtem is working, uncomment the rating attributes
 	 				while (rs.next()) {
 	 					MyEventBean myevent = new MyEventBean();

 	 					

 	 					myevent.setEventname(rs.getString(1));
 	 					myevent.setStamp(rs.getTimestamp(2));
 	 					myevent.setnGuestspots(rs.getInt(3));
 	 					myevent.setnParticipants(rs.getInt(4));
 	 					myevent.setRating(rs.getInt(5));
 	 					hostpastList.add(myevent);

 	  					// Debug
 	 					System.out.println(myevent.getEventname());
 	 					System.out.println(myevent.getStamp());
 	 					System.out.println(myevent.getnGuestspots());
 	 					System.out.println(myevent.getnParticipants());
 	 					
 	 					}
 	 			}
 	 			catch (Exception ex) {
 	 				System.out.println("FAIL!: An Exception has occurred on gethostpast! " + ex);
 	 			}
 	 			
 	 			finally {
 	 				if (rs != null) {
 	 					try {
 	 						rs.close();
 	 					} catch (Exception e) {
 	 					}
 	 					rs = null;
 	 				}

 	 				if (stmt != null) {
 	 					try {
 	 						stmt.close();
 	 					} catch (Exception e) {
 	 					}
 	 					stmt = null;
 	 				}

 	 				if (currentCon != null) {
 	 					try {
 	 						currentCon.close();
 	 					} catch (Exception e) {
 	 					}

 	 					currentCon = null;
 	 				}
 	 			}

 	 			
 	 			return hostpastList;

 	 		
 		}

public static ArrayList<MyEventBean> getguestupcoming(UserBean user) {
 		
 	 	// preparing some objects for connection
 	 			Statement stmt = null;

 	 			String username = user.getEmail();
 	 			String searchQuery = null; 
                
 	 			//uses attend, event and users entities
 	 		
 	 			searchQuery = " select name, starttimestamp, numberguestspots, numberparticipants, host, hostname,  rating, guestrating as myrating from(select * from (select E.name, E.starttimestamp, E.host, E.numberguestspots, E.numberparticipants, A.guestrating from attend as A join event as E on A.host=E.host and A.eventtimestamp=E.starttimestamp where guest='"+ username +"' and E.isfinished='false') as table1 natural join (select host, avg(hostrating) as rating from attend group by host) as table2) as A, (select name as hostname, email from users) as B where A.host=B.email";
 	 					
 	 					
 	 			
 	 			System.out.println("Fetching the event for " + username);
 	 			System.out.println("Query: " + searchQuery);

 	 			ArrayList<MyEventBean> guestupcomingList = new ArrayList<MyEventBean>();
 	 			
 	 
 	 			try {
 	 				// connect to DB
 	 				currentCon = ConnectionManager.getConnection();
 	 				stmt = currentCon.createStatement();
 	 				rs = stmt.executeQuery(searchQuery);

 	 			  //once the rating sysmtem is working, uncomment the rating attributes
 	 				while (rs.next()) {
 	 					MyEventBean myevent = new MyEventBean();

 	 					

 	 					myevent.setEventname(rs.getString(1));
 	 					myevent.setStamp(rs.getTimestamp(2));
 	 					myevent.setnGuestspots(rs.getInt(3));
 	 					myevent.setnParticipants(rs.getInt(4));
 	 					myevent.setHost(rs.getString(5));
 	 					myevent.sethostName(rs.getString(6));
 	 					myevent.sethostavgRating(rs.getInt(7));
 	 					guestupcomingList.add(myevent);

 	  					// Debug
 	 					System.out.println(myevent.getEventname());
 	 					System.out.println(myevent.getStamp());
 	 					System.out.println(myevent.getnGuestspots());
 	 					System.out.println(myevent.getnParticipants());
 	 					System.out.println(myevent.getRating());
 	 					}
 	 			}
 	 			catch (Exception ex) {
 	 				System.out.println("FAIL!: An Exception has occurred on getguestupcoming! " + ex);
 	 			}
 	 			
 	 			finally {
 	 				if (rs != null) {
 	 					try {
 	 						rs.close();
 	 					} catch (Exception e) {
 	 					}
 	 					rs = null;
 	 				}

 	 				if (stmt != null) {
 	 					try {
 	 						stmt.close();
 	 					} catch (Exception e) {
 	 					}
 	 					stmt = null;
 	 				}

 	 				if (currentCon != null) {
 	 					try {
 	 						currentCon.close();
 	 					} catch (Exception e) {
 	 					}

 	 					currentCon = null;
 	 				}
 	 			}

 	 			
 	 			return guestupcomingList;

 	 		
 		}

public static ArrayList<MyEventBean> getguestpast(UserBean user) {
		
	 	// preparing some objects for connection
	 			Statement stmt = null;

	 			String username = user.getEmail();
	 			String searchQuery = null; 
            
	 			
	 			//once the rating system is working we have to change this query
	 			searchQuery = "select name, starttimestamp, numberguestspots, numberparticipants, host, hostname,  rating, guestrating as myrating from(select * from (select E.name, E.starttimestamp, E.host, E.numberguestspots, E.numberparticipants, A.guestrating from attend as A join event as E on A.host=E.host and A.eventtimestamp=E.starttimestamp where guest='"+ username +"' and E.isfinished='true') as table1 natural join (select host, avg(hostrating) as rating from attend group by host) as table2) as A, (select name as hostname, email from users) as B where A.host=B.email";
	 			
	 			System.out.println("Fetching the event for " + username);
	 			System.out.println("Query: " + searchQuery);

	 			ArrayList<MyEventBean> guestpastList = new ArrayList<MyEventBean>();
	 			
	 
	 			try {
	 				// connect to DB
	 				currentCon = ConnectionManager.getConnection();
	 				stmt = currentCon.createStatement();
	 				rs = stmt.executeQuery(searchQuery);

	 			  //once the rating sysmtem is working, uncomment the rating attributes
	 				while (rs.next()) {
	 					MyEventBean myevent = new MyEventBean();

	 					

	 					myevent.setEventname(rs.getString(1));
	 					myevent.setStamp(rs.getTimestamp(2));
	 					myevent.setnGuestspots(rs.getInt(3));
	 					myevent.setnParticipants(rs.getInt(4));
	 					myevent.setHost(rs.getString(5));
	 					myevent.sethostName(rs.getString(6));
	 					myevent.sethostavgRating(rs.getInt(7));
	 					myevent.setRating(rs.getInt(8));
	 					guestpastList.add(myevent);

	  					// Debug
	 					System.out.println(myevent.getEventname());
	 					System.out.println(myevent.getStamp());
	 					System.out.println(myevent.getnGuestspots());
	 					System.out.println(myevent.getnParticipants());
	 					
	 					}
	 			}
	 			catch (Exception ex) {
	 				System.out.println("FAIL!: An Exception has occurred on getguestpast! " + ex);
	 			}
	 			
	 			finally {
	 				if (rs != null) {
	 					try {
	 						rs.close();
	 					} catch (Exception e) {
	 					}
	 					rs = null;
	 				}

	 				if (stmt != null) {
	 					try {
	 						stmt.close();
	 					} catch (Exception e) {
	 					}
	 					stmt = null;
	 				}

	 				if (currentCon != null) {
	 					try {
	 						currentCon.close();
	 					} catch (Exception e) {
	 					}

	 					currentCon = null;
	 				}
	 			}

	 			
	 			return guestpastList;

	 		
		}


 	
	
}
